package tx.yt.charge.service;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import tx.yt.charge.model.Orderinfo;
import tx.yt.charge.utils.DateUtil;
import tx.yt.charge.utils.PageUtil;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 订单明细表业务
 * Created by ZJ on 2015/1/14.
 */
@Service
public class OrderinfoService {
    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据订单编号查询订单信息
     *
     * @param id
     * @return
     */
    public Orderinfo queryOrderinfo(int id) {
        final Orderinfo orderinfo = new Orderinfo();
        String sql = "select id,orderNum,weight,customerId,destination,freight,cost,businessMan,createTime,modifyTime from orderinfo where id = ?";
        this.jdbcTemplate.query(sql, new Object[]{id}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                orderinfo.setId(resultSet.getInt("id"));
                orderinfo.setOrderNum(resultSet.getString("orderNum"));
                orderinfo.setWeight(resultSet.getString("weight"));
                orderinfo.setCustomerId(resultSet.getString("customerId"));
                orderinfo.setDestination(resultSet.getString("destination"));
                orderinfo.setFreight(resultSet.getString("freight"));
                orderinfo.setCost(resultSet.getDouble("cost"));
                orderinfo.setBusinessMan(resultSet.getString("businessMan"));
                orderinfo.setCreateTime(resultSet.getDate("createTime"));
                orderinfo.setModifyTime(resultSet.getDate("modifyTime"));
            }
        });
        return orderinfo;
    }

    /**
     * 根据订单信息查询订单列表
     *
     * @param orderinfo
     * @return
     */
    public List<Orderinfo> queryOrderinfos(Orderinfo orderinfo) {
        StringBuilder sql = new StringBuilder("select id,orderNum,weight,customerId,destination,freight,cost,businessMan,createTime,modifyTime from orderinfo where 1 = 1");
        if (0 != orderinfo.getId()) {
            sql.append(" and id = " + orderinfo.getId());
        }
        if (!StringUtils.isEmpty(orderinfo.getOrderNum())) {
            sql.append(" and orderNum = '" + orderinfo.getOrderNum() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getWeight())) {
            sql.append(" and weight = '" + orderinfo.getWeight() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getCustomerId())) {
            sql.append(" and customerId = '" + orderinfo.getCustomerId() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getDestination())) {
            sql.append(" and destination = '" + orderinfo.getDestination() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getFreight())) {
            sql.append(" and freight = '" + orderinfo.getFreight() + "'");
        }
        if (0.0 != orderinfo.getCost()) {
            sql.append(" and cost = " + orderinfo.getCost());
        }
        if (!StringUtils.isEmpty(orderinfo.getBusinessMan())) {
            sql.append(" and businessMan = '" + orderinfo.getBusinessMan() + "'");
        }

        List<Orderinfo> result = new ArrayList<Orderinfo>();
        Orderinfo ord = null;
        List lists = this.jdbcTemplate.queryForList(sql.toString());
        Iterator it = lists.iterator();
        while (it.hasNext()) {
            Map map = (Map) it.next();
            ord = new Orderinfo();
            ord.setId(Integer.valueOf(String.valueOf(map.get("id"))));
            ord.setOrderNum((String) map.get("orderNum"));
            ord.setWeight((String) map.get("weight"));
            ord.setCustomerId((String) map.get("customerId"));
            ord.setDestination((String) map.get("destination"));
            ord.setFreight((String) map.get("freight"));
            ord.setCost(Double.valueOf(String.valueOf(map.get("cost"))));
            ord.setBusinessMan((String) map.get("businessMan"));
            ord.setCreateTime(DateUtil.parseObject(map.get("createTime")));
            ord.setModifyTime(DateUtil.parseObject(map.get("modifyTime")));
            result.add(ord);
        }

        return result;
    }

    /**
     * 根据订单信息查询订单列表(分页)
     *
     * @param orderinfo
     * @param currentPage
     * @return
     */
    public List<Orderinfo> queryOrderinfos(Orderinfo orderinfo, int currentPage) {
        StringBuilder sql = new StringBuilder("select id,orderNum,weight,customerId,destination,freight,cost,businessMan,createTime,modifyTime from orderinfo where 1 = 1");
        if (0 != orderinfo.getId()) {
            sql.append(" and id = " + orderinfo.getId());
        }
        if (!StringUtils.isEmpty(orderinfo.getOrderNum())) {
            sql.append(" and orderNum = '" + orderinfo.getOrderNum() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getWeight())) {
            sql.append(" and weight = '" + orderinfo.getWeight() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getCustomerId())) {
            sql.append(" and customerId = '" + orderinfo.getCustomerId() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getDestination())) {
            sql.append(" and destination = '" + orderinfo.getDestination() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getFreight())) {
            sql.append(" and freight = '" + orderinfo.getFreight() + "'");
        }
        if (0.0 != orderinfo.getCost()) {
            sql.append(" and cost = " + orderinfo.getCost());
        }
        if (!StringUtils.isEmpty(orderinfo.getBusinessMan())) {
            sql.append(" and businessMan = '" + orderinfo.getBusinessMan() + "'");
        }
        sql.append(" order by id desc limit " + PageUtil.getStartRow(currentPage, PageUtil.PAGESIZE) + "," + PageUtil.PAGESIZE + "");

        List<Orderinfo> result = new ArrayList<Orderinfo>();
        Orderinfo ord = null;
        List lists = this.jdbcTemplate.queryForList(sql.toString());
        Iterator it = lists.iterator();
        while (it.hasNext()) {
            Map map = (Map) it.next();
            ord = new Orderinfo();
            ord.setId(Integer.valueOf(String.valueOf(map.get("id"))));
            ord.setOrderNum((String) map.get("orderNum"));
            ord.setWeight((String) map.get("weight"));
            ord.setCustomerId((String) map.get("customerId"));
            ord.setDestination((String) map.get("destination"));
            ord.setFreight((String) map.get("freight"));
            ord.setCost(Double.valueOf(String.valueOf(map.get("cost"))));
            ord.setBusinessMan((String) map.get("businessMan"));
            ord.setCreateTime(DateUtil.parseObject(map.get("createTime")));
            ord.setModifyTime(DateUtil.parseObject(map.get("modifyTime")));
            result.add(ord);
        }

        return result;
    }

    /**
     * 根据订单信息统计订单量(分页)
     *
     * @param orderinfo
     * @return
     */
    public int queryOrderinfoCount(Orderinfo orderinfo) {
        StringBuilder sql = new StringBuilder("select id from orderinfo where 1 = 1");
        if (0 != orderinfo.getId()) {
            sql.append(" and id = " + orderinfo.getId());
        }
        if (!StringUtils.isEmpty(orderinfo.getOrderNum())) {
            sql.append(" and orderNum = '" + orderinfo.getOrderNum() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getWeight())) {
            sql.append(" and weight = '" + orderinfo.getWeight() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getCustomerId())) {
            sql.append(" and customerId = '" + orderinfo.getCustomerId() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getDestination())) {
            sql.append(" and destination = '" + orderinfo.getDestination() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getFreight())) {
            sql.append(" and freight = '" + orderinfo.getFreight() + "'");
        }
        if (0.0 != orderinfo.getCost()) {
            sql.append(" and cost = " + orderinfo.getCost());
        }
        if (!StringUtils.isEmpty(orderinfo.getBusinessMan())) {
            sql.append(" and businessMan = '" + orderinfo.getBusinessMan() + "'");
        }

        List lists = this.jdbcTemplate.queryForList(sql.toString());
        return lists.size();
    }

    /**
     * 订单总页数
     *
     * @return
     */
    public int orderinfoTotalPageNum(int orderinfoCount) {
        return PageUtil.getTotalPageNum(orderinfoCount, PageUtil.PAGESIZE);
    }

    /**
     * 添加订单
     *
     * @param orderinfo
     * @return
     */
    public boolean addOrderinfo(Orderinfo orderinfo) {
        String sql = "insert into orderinfo(orderNum,weight,customerId,destination,freight,cost,businessMan,createTime,modifyTime) value (?,?,?,?,?,?,?,?,?);";
        this.jdbcTemplate.update(sql, new Object[]{orderinfo.getOrderNum(), orderinfo.getWeight(), orderinfo.getCustomerId(), orderinfo.getDestination(), orderinfo.getFreight(), orderinfo.getCost(), orderinfo.getBusinessMan(), DateUtil.getDefaultFormatedDate(), DateUtil.getDefaultFormatedDate()});
        return true;
    }

    /**
     * 修改订单
     *
     * @param orderinfo
     * @return
     */
    public boolean modifyOrderinfo(Orderinfo orderinfo) {
        StringBuilder sql = new StringBuilder("update orderinfo set modifyTime = ?");
        if (!StringUtils.isEmpty(orderinfo.getOrderNum())) {
            sql.append(" , orderNum = '" + orderinfo.getOrderNum() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getWeight())) {
            sql.append(" , weight = '" + orderinfo.getWeight() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getCustomerId())) {
            sql.append(" , customerId = '" + orderinfo.getCustomerId() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getDestination())) {
            sql.append(" , destination = '" + orderinfo.getDestination() + "'");
        }
        if (!StringUtils.isEmpty(orderinfo.getFreight())) {
            sql.append(" , freight = '" + orderinfo.getFreight() + "'");
        }
        if (0.0 != orderinfo.getCost()) {
            sql.append(" , cost = " + orderinfo.getCost());
        }
        if (!StringUtils.isEmpty(orderinfo.getBusinessMan())) {
            sql.append(" , businessMan = '" + orderinfo.getBusinessMan() + "'");
        }
        sql.append(" where id = " + orderinfo.getId());
        this.jdbcTemplate.update(sql.toString(), new Object[]{DateUtil.getCurrentDate()});
        return true;
    }

    /**
     * 根据订单编号删除订单
     *
     * @param id
     * @return
     */
    public boolean removeOrderinfo(int id) {
        String sql = "delete from orderinfo where id = ?";
        this.jdbcTemplate.update(sql, new Object[]{id});
        return true;
    }
}